<?php
/*
Template Name: MyStats
*/

### Function: Get Authors Stats
function getAuthorStats($before = '', $between = '', $after = '')
{
    global $wpdb;
    $postsauthor = $wpdb->get_results("SELECT u.display_name as name, count(p.post_author) as count FROM $wpdb->posts p, $wpdb->users u where p.post_author = u.ID and p.post_status = 'publish' and p.post_type = 'post' group by post_author order by count DESC, name", ARRAY_A);
	if($postsauthor) {
		foreach ($postsauthor as $stat) {
			$username = htmlspecialchars(stripslashes($stat['name']));
			$postcount = htmlspecialchars(stripslashes($stat['count']));
			echo $before.$username.$between.$postcount.$after;
		}
	} else {
		echo '<li>'.__('N/A').'</li>';
	}
}

### Function: Get Comments' Members Stats
function getCommentStats($before = '', $between = '', $after = '')
{
	global $wpdb;
	$comments = $wpdb->get_results("SELECT comment_author, COUNT(comment_ID) AS 'comment_total' FROM $wpdb->comments WHERE comment_approved = '1' GROUP BY comment_author ORDER BY comment_total DESC, comment_author ASC");
	if($comments) {
		foreach ($comments as $comment) {
			$comment_author = strip_tags(stripslashes($comment->comment_author));
			$comment_author_link = urlencode($comment_author);
			$comment_total = intval($comment->comment_total);
			echo $before.$comment_author.$between.$comment_total.$after;
		}
	} else {
		echo $before.__('N/A').$after;
	}
}

function getPostCountByMonth($before = '', $between = '', $after = '')
{
	global $wpdb;
	$data = $wpdb->get_results("SELECT count( id ) as 'count' , monthname(post_date) as 'month' , year(post_date) as 'year' FROM $wpdb->posts where post_status = 'publish' and post_type = 'post' and month(post_date) <> 0 GROUP BY year(post_date), month(post_date) ORDER BY post_date DESC");
	echo $before."<strong>Mois</strong>".$between."<strong>Année</strong>".$between."<strong>Nombre</strong>".$after;
	if ($data) {
		foreach ($data as $line) {
			echo $before.$line->month.$between.$line->year.$between.$line->count.$after;
		}
	}
}

function getCommentCountByMonth($before = '', $between = '', $after = '')
{
	global $wpdb;
	$data = $wpdb->get_results("SELECT count( comment_id ) as 'count' , monthname(comment_date) as 'month' , year(comment_date) as 'year' FROM $wpdb->comments where comment_approved = '1' and month(comment_date) <> 0 GROUP BY year(comment_date), month(comment_date) ORDER BY comment_date DESC");
	echo $before."<strong>Mois</strong>".$between."<strong>Année</strong>".$between."<strong>Nombre</strong>".$after;
	if ($data) {
		foreach ($data as $line) {
			echo $before.$line->month.$between.$line->year.$between.$line->count.$after;
		}
	}
}

function getRatingsCountByAuthor($before = '', $between = '', $after = '')
{
	global $wpdb;
	$query = "SELECT rating_username as author, COUNT(*) AS ratings ";
	$query .= "FROM " . $wpdb->prefix . "ratings ";
	$query .= "GROUP BY rating_username ";
	$query .= "ORDER BY ratings DESC ";
//	$query .= "LIMIT 0 , 30 ";
	$data = $wpdb->get_results($query);
	echo $before."<strong>Auteur</strong>".$between."<strong>Nombre</strong>".$after;
	if ($data) {
		foreach ($data as $line) {
			echo $before . $line->author . $between . $line->ratings . $after;
		}
	}
}

### Function: Get Total Posts
function get_totalposts()
{
	global $wpdb;
	$totalposts = $wpdb->get_var("SELECT COUNT(ID) FROM $wpdb->posts WHERE post_status = 'publish' and post_type = 'post'");
	echo $totalposts;
}

### Function: Get Total Pages
function get_totalpages()
{
	global $wpdb;
	$totalpages = $wpdb->get_var("SELECT COUNT(ID) FROM $wpdb->posts WHERE post_status = 'static' and post_type = 'post'");
	echo $totalpages;
}

### Function: Get Total Comments
function get_totalcomments()
{
	global $wpdb;
	$totalcomments = $wpdb->get_var("SELECT COUNT(comment_ID) FROM $wpdb->comments WHERE comment_approved = '1'");
	echo $totalcomments;
}

$table_size = '450';
?>

<?php get_header(); ?>

	<div class="post" id="stats-articles">
		<div class="post-header">
			<h3 class="post-title">Statistiques des articles</h3>
		</div><!-- End div post-header -->
		<div class="post-content">
	    	    <p align="center"><strong>Nombre total d'articles : <?php get_totalposts(); ?></strong></p>
		    <table width="<?php echo $table_size; ?>" align="center">
		    	<?php getAuthorStats("<tr><td width=\"250\"><strong>", "</strong></td><td align=\"right\" width=\"200\">", " Article(s)</td></tr>"); ?>
		    </table>
		</div><!-- End div post-content -->
	</div><!-- End div post -->

	<div class="post" id="stats-comments">
		<div class="post-header">
			<h3 class="post-title">Statistiques des commentaires</h3>
		</div><!-- End div post-header -->
		<div class="post-content">
	    	<p align="center"><strong>Nombre total de commentaires : <?php get_totalcomments(); ?></strong></p>
		    <table width="<?php echo $table_size; ?>" align="center">
<?php

	$comments = $wpdb->get_results("SELECT comment_author, comment_author_email, COUNT(comment_ID) AS 'comment_total' FROM $wpdb->comments WHERE comment_approved = '1' GROUP BY comment_author ORDER BY comment_total DESC, comment_author ASC");
	if($comments) {
		foreach ($comments as $comment) {
			$comment_author = strip_tags(stripslashes($comment->comment_author));
			//$comment_author_link = urlencode($comment_author);
			$comment_total = intval($comment->comment_total);
			echo '<tr><td width="30">'.get_avatar( $comment->comment_author_email, 30, '', 'Configurer votre image sur http://fr.gravatar.com' ).'</td><td width="220"><strong>'.$comment_author.'</strong></td><td align="right" width=\"150\">'.$comment_total.' Commentaire(s)</td></tr>';
		}
	} else {
		echo $before.__('N/A').$after;
	}
?>
<?php /*get_avatar( get_comment_author_email(), 50, '', 'Configurer votre image sur http://fr.gravatar.com' );*/ ?>
		    </table>
		</div><!-- End div post-content -->
	</div><!-- End div post -->

	<div class="post" id="stats-articles-bymonth">
		<div class="post-header">
			<h3 class="post-title">R&eacute;partition des articles dans le temps</h3>
		</div><!-- End div post-header -->
		<div class="post-content">
		    <table width="<?php echo $table_size; ?>" align="center">
		    	<?php getPostCountByMonth("<tr><td width=\"250\">", "</td><td align=\"center\" width=\"150\">", "</td></tr>"); ?>
		    </table>
		</div><!-- End div post-content -->
	</div><!-- End div post -->

	<div class="post" id="stats-comments-bymonth">
		<div class="post-header">
			<h3 class="post-title">R&eacute;partition des commentaires dans le temps</h3>
		</div><!-- End div post-header -->
		<div class="post-content">
		    <table width="<?php echo $table_size; ?>" align="center">
		    	<?php getCommentCountByMonth("<tr><td width=\"250\">", "</td><td align=\"center\" width=\"150\">", "</td></tr>"); ?>
		    </table>
		</div><!-- End div post-content -->
	</div><!-- End div post -->

	<div class="post" id="stats-ratings">
		<div class="post-header">
			<h3 class="post-title">Statistiques des notes</h3>
		</div><!-- End div post-header -->
		<div class="post-content">
		    <table width="<?php echo $table_size; ?>" align="center">
		    	<?php getRatingsCountByAuthor("<tr><td width=\"250\">", "</td><td align=\"center\" width=\"150\">", "</td></tr>"); ?>
		    </table>
		</div><!-- End div post-content -->
	</div><!-- End div post -->

<?php get_footer(); ?>
